package Dao;

import Service.DepartmentService;
import Service.SchoolService;
import org.example.Department;
import org.example.School;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public class DepartmentDao  {
    private static final DepartmentDao departmentDao =new DepartmentDao();
    public static DepartmentDao getInstance() {
        return departmentDao;
    }


    public Collection<Department> findAll(Connection connection, List<util.Condition> conditionList, Pagination pagination) throws SQLException {
        Collection<Department> desiredDepartments = new HashSet<Department>();
        //准备预编译的语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM department");
        if(conditionList!=null){
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        int totalNum =CommonDao.getInstance().count(connection,"department",conditionList);
        String pginationClause = pagination.toLimitClause(totalNum);
        sql_select.append(pginationClause);
        PreparedStatement pstmt_select_all = connection.prepareStatement(sql_select.toString());
        //执行预编译语句，结果集保存在resultSet中
        ResultSet resultSet_select_all = pstmt_select_all.executeQuery();
        //遍历resultSet
        while (resultSet_select_all.next()) {
            int id = resultSet_select_all.getInt("id");
            String description = resultSet_select_all.getString("description");
            String no = resultSet_select_all.getString("no");
            String remarks = resultSet_select_all.getString("remarks");
            int school_id = resultSet_select_all.getInt("school_id");
            School school = SchoolService.getInstance().find(school_id);
            Department department = new Department(id, description,no,remarks,school,school_id);
            desiredDepartments.add(department);
        }
        JdbcHelper.close(resultSet_select_all,pstmt_select_all,connection);
        return desiredDepartments;
    }

    public Department add( Department department) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        String addDepartment_sql = "INSERT INTO department ( id,description,no,remarks, school_id)VALUES"+"(?,?,?,?,?)";
        PreparedStatement stmt_add_all = connection.prepareStatement(addDepartment_sql);
        stmt_add_all.setInt(1,department.getId());
        stmt_add_all.setString(2,department.getDescription());
        stmt_add_all.setString(3,department.getNo());
        stmt_add_all.setString(4,department.getRemarks());
        stmt_add_all.setInt(5,department.getSchool_id());
        stmt_add_all.executeUpdate();
        JdbcHelper.close(stmt_add_all,connection);
        //关闭connection对象。只要建立了连接，必须要关闭。
        return null;
    }


    public Department update(Department department) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        String updateDepartment_sql1 = "update department set description = ? ,school_id = ? where id = ?";
        PreparedStatement stmt_update_name = connection.prepareStatement(updateDepartment_sql1);
        stmt_update_name.setString(1,department.getDescription());
        stmt_update_name.setInt(2,department.getSchool_id());
        stmt_update_name.setInt(3,department.getId());
        stmt_update_name.executeUpdate();
        JdbcHelper.close(stmt_update_name,connection);
        return null;
    }


    public Department delete(Department department) throws SQLException {
        Connection connection = JdbcHelper.getConn();
        String updateDepartment_sql1 = "delete from department where id = ?";
        PreparedStatement stmt_update_name = connection.prepareStatement(updateDepartment_sql1);
        stmt_update_name.setInt(1,department.getId());
        stmt_update_name.executeUpdate();
        JdbcHelper.close(stmt_update_name,connection);
        return null;
    }

}